//
//  DataBaseManager.m
//  UI22_数据库
//
//  Created by 才诗琦 on 16/1/7.
//  Copyright © 2016年 www.lanou3g.com. All rights reserved.
//

#import "DataBaseManager.h"
#import "Movie.h"

//把一个字符串当做类名使用
@class liuiu;
@implementation DataBaseManager
+(DataBaseManager *)shareInstance
{
    
    //定义一个静态指针变量  保证指针只有一个
    static DataBaseManager *dbManger = nil;
    
    //判断 是否是第一次调用该方法
    
    //如果指针为空  创建对象
    if (!dbManger) {
        
        //单例中的alloc 不需要释放(release)
        dbManger = [[DataBaseManager alloc] init];
    }
    
    //如果指针不为空 直接返回
    return  dbManger;
    
}

//打开数据库
- (void)openDB
{
    
//    数据库一般放在Documents下
    
    //后缀: db/sqlite
   NSString *path = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject  stringByAppendingPathComponent:@"suibian.db"];
    
    //创建数据库文件
    
    //判断dbPoint指针中 是否存在数据库指针 如果不存在 再创建
    
    //参数1: 数据库路径  UTF-8转码
    //参数2: 数据库指针地址
    
    int result = sqlite3_open(path.UTF8String, &dbPoint);
    
    NSLog(@"打开数据:%d, 路径:%@", result, path);
}

///关闭数据库
- (void)closeDB
{
    
    sqlite3_close(dbPoint);
}

///创建表
- (void)createTable
{
    //1.SQL语句
    
    //exists 存在
    //text 字符串
    //integer 整数
    //primary key 主键
    //autoincrement  自动增加
    
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS movie(movieName text, pubdate text, movieId integer PRIMARY KEY AUTOINCREMENT)";
    // 2.执行语句
    // 参数1:数据库指针
    // 参数2:SQL 语句
    int result =  sqlite3_exec(dbPoint, createSQL.UTF8String, NULL, NULL, NULL);
    // 3.判断操作
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"失败:%d", result);
    }
    
}
///删除表
- (void)dropTable
{
    //1.SQL语句
    NSString *dropSQL =@"DROP TABLE movie";
    //2.执行
    int result = sqlite3_exec(dbPoint, dropSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    
    [self judgeResult:result massage:@"删除表"];
    
    
    
}
///判断方法

//参数1: 判断的数值
//参数2:打印信息
- (void)judgeResult:(int)r massage:(NSString *)mes
{
    
    if (r == SQLITE_OK) {
        NSLog(@"%@成功", mes);
    }
    
    else{
        NSLog(@"%@失败:%d", mes, r);
        
    }
    
}

///增
- (void)insertMovie:(Movie *)m
{
    
    //1.SQL语句
NSString *insertSQL =[NSString stringWithFormat: @"INSERT INTO movie(movieName, pubDate) values('%@', '%@')", m.movieName, m.pubDate];
    
    //2.执行
    
    int result = sqlite3_exec(dbPoint, insertSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    
     [self judgeResult:result massage:@"插入数据"];
}

///删
- (void)deleteMovieWithId:(NSInteger)movieId
{//1.SQL语句
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM movie WHERE movieId = '%ld'", movieId];
    
  //2.执行
    int result = sqlite3_exec(dbPoint, deleteSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self judgeResult:result massage:@"删除数据"];
    
}

- (void)updateMovie:(Movie *)newM WithId:(NSInteger)movieId
{
    NSString *updateSQL = [NSString stringWithFormat:@"UPDATE MOVIE SET movieName = '%@', pubDate = '%@' WHERE MOVIEID = '%ld'",newM.movieName, newM.pubDate,movieId];
    //2.执行
    int result = sqlite3_exec(dbPoint, updateSQL.UTF8String, NULL, NULL, NULL);
    //3.判断
    [self judgeResult:result massage:@"更新数据"];
    
}


- (NSMutableArray *)select
{
    //查询后数据处理过程
    //1.获取到所有的数据
    //2.先遍历行 得到每一条数据
    //3.再遍历当前行的每一列  按照对应关系保存在model中
    //4.model添加到数组 返回
    
    //1.SQL语句
   NSString *selectSQL = @"SELECT * FROM movie";
    
    //2.创建数据库替身
    sqlite3_stmt *stmt = nil;
    //3.准备SQL语句
    
    //prepare_v2函数 那数据库对象/SQL语句/数据库替身  关联在一起
    int result = sqlite3_prepare_v2(dbPoint, selectSQL.UTF8String , -1, &stmt, nil);
    //4.初始化返回的数组
    NSMutableArray *arr = [NSMutableArray array];
    
    
    //4.判断准备是否成功
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    //6.遍历每一行数据
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //逐行获取每一列信息
            //列数从0开始
        
           const unsigned char *mName = sqlite3_column_text(stmt, 0);
            
            const unsigned char *pubDate = sqlite3_column_text(stmt, 1);
            
            int mId = sqlite3_column_int(stmt, 2);
            
            //创建model对象
            
            Movie *m = [[Movie alloc] init];
            
            m.movieName = [NSString stringWithUTF8String:(const char *)mName];
            m.pubDate = [NSString stringWithUTF8String:(const char *)pubDate];
            m.movieId = mId;
            
            //添加到数组
            
            [arr addObject:m];
        }
    
            
    }
    else{
        
        NSLog(@"失败:%d", result);
    }
    
    //返回数据
    
    return arr;
}
@end
